﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.Data;
namespace QLSinhVien
{
    /// <summary>
    /// Summary description for QL
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class QL : System.Web.Services.WebService
    {

        string datasource = "THANHCONG";
        string user = "sa";
        string password = "12345";
        string Namedatabase = "DOANODD";

        [WebMethod]
        public void ThemSinhVien(int MSSV, string hoten, string ngaysinh, string gioitinh, string diachi, string email, string madoituong, string manganh)
        {
            SqlConnection con = new SqlConnection("Data Source =" + datasource + " ;user id = " + user + "; password = " + password + ";database = " + Namedatabase);
            con.Open();
            string ADD = "insert into SINHVIEN values(" + MSSV + ",N'" + hoten + "','" + ngaysinh + "',N'" + gioitinh + "',N'" + diachi + "',N'" + email + "',N'" + madoituong + "',N'" + manganh + "')";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = ADD;
            cmd.ExecuteNonQuery();
            con.Close();

        }
        [WebMethod(Description = "Xoa sinh vien")]
        public void XoaSinhVien(int MSSV)
        {
            SqlConnection con = new SqlConnection("Data Source =" + datasource + " ;user id = " + user + "; password = " + password + ";database = " + Namedatabase);
            con.Open();
            string ADD = "delete SINHVIEN where MSSV = " + MSSV;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = ADD;
            cmd.ExecuteNonQuery();
            con.Close();

        }
        [WebMethod(Description = "Sua sinh vien")]
        public void SuaSinhVien(int MSSV, string hoten, string ngaysinh, string gioitinh, string diachi, string email, string madoituong, string manganh)
        {
            SqlConnection con = new SqlConnection("Data Source =" + datasource + " ;user id = " + user + "; password = " + password + ";database = " + Namedatabase);
            con.Open();
            string ADD = "update SINHVIEN set HoTen =N'" + hoten + "',NgaySinh='" + ngaysinh + "',GioiTinh=N'" + gioitinh + "'" +
                ",DiaChi=N'" + diachi + "',Email = N'" + email + "',MaDoiTuong = N'" + madoituong + "',MaNganh = N'" + manganh +
            "' where MSSV = " + MSSV;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = ADD;
            cmd.ExecuteNonQuery();
            con.Close();

        }
        [WebMethod]
        public DataSet ThongTinSinhVien(int MSSV)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter da;
            string query = "select MSSV,HoTen,GioiTinh,NgaySinh,DOITUONG.TenDoiTuong,NGANH.TenNganh  from SINHVIEN,DOITUONG,NGANH where SINHVIEN.MaDoiTuong = DOITUONG.MaDoiTuong and NGANH.MaNganh = SINHVIEN.MaNganh and MSSV = " + MSSV;
            SqlConnection con = new SqlConnection("Data Source =" + datasource + " ;user id = " + user + "; password = " + password + ";database = " + Namedatabase);
            da = new SqlDataAdapter(query, con);
            da.Fill(ds, "SinhVien");
            return ds;
        }
        [WebMethod]
        public DataSet ThongTinDangKyMonHoc(int MSSV)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter da;
            string query = "select Mamon, TenMonHoc,SoTCLT,SoTCTH from MONHOC ,SINHVIEN where MONHOC.MaNganh = SINHVIEN.MaNganh and MSSV = "+ MSSV;
            SqlConnection con = new SqlConnection("Data Source =" + datasource + " ;user id = " + user + "; password = " + password + ";database = " + Namedatabase);
            da = new SqlDataAdapter(query, con);
            da.Fill(ds, "SinhVien");
            return ds;
        }
        /*********************************************************************************************************/
        [WebMethod]
        public void ThemNganh(string MaNganh, string TenNganh, string MaKhoa)
        {
            SqlConnection con = new SqlConnection("Data Source =" + datasource + " ;user id = " + user + "; password = " + password + ";database = " + Namedatabase);
            con.Open();
            string ADD = "insert into NGANH values('" + MaNganh + "','" + TenNganh + "','" + MaKhoa + "') ";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = ADD;
            cmd.ExecuteNonQuery();
            con.Close();

        }
        [WebMethod]
        public void SuaNganh(string MaNganh, string TenNganh, string MaKhoa)
        {
            SqlConnection con = new SqlConnection("Data Source =" + datasource + " ;user id = " + user + "; password = " + password + ";database = " + Namedatabase);
            con.Open();
            string ADD = "update NGANH set TenNganh = '" + TenNganh + "',MaKhoa ='" + MaKhoa + "' where MaNganh = '" + MaNganh + "'";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = ADD;
            cmd.ExecuteNonQuery();
            con.Close();

        }
        [WebMethod]
        public void XoaNganh(String MaNganh)
        {
            SqlConnection con = new SqlConnection("Data Source =" + datasource + " ;user id = " + user + "; password = " + password + ";database = " + Namedatabase);
            con.Open();
            string ADD = "delete NGANH where MaNganh = '" + MaNganh + "'";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = ADD;
            cmd.ExecuteNonQuery();
            con.Close();

        }
        /***************************************KHOA****************************************************************/
        [WebMethod]
        public void ThemKhoa(string MaKhoa, string TenKhoa)
        {
            SqlConnection con = new SqlConnection("Data Source =" + datasource + " ;user id = " + user + "; password = " + password + ";database = " + Namedatabase);
            con.Open();
            string ADD = "insert into KHOA values('" + MaKhoa + "','" + TenKhoa + "') ";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = ADD;
            cmd.ExecuteNonQuery();
            con.Close();

        }
        [WebMethod]
        public void SuaKhoa(string MaKhoa, string TenKhoa)
        {
            SqlConnection con = new SqlConnection("Data Source =" + datasource + " ;user id = " + user + "; password = " + password + ";database = " + Namedatabase);
            con.Open();
            string ADD = "update KHOA set TenKhoa = '" + TenKhoa + "' where MaKhoa = '" + MaKhoa + "'";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = ADD;
            cmd.ExecuteNonQuery();
            con.Close();

        }
        [WebMethod]
        public void XoaKhoa(string MaKhoa)
        {
            SqlConnection con = new SqlConnection("Data Source =" + datasource + " ;user id = " + user + "; password = " + password + ";database = " + Namedatabase);
            con.Open();
            string ADD = "delete KHOA where MaKhoa = '" + MaKhoa + "'";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = ADD;
            cmd.ExecuteNonQuery();
            con.Close();

        }
        /***************************************MON HOC****************************************************************/
        [WebMethod]
        public void ThemMonHoc(string MaMonHoc, string TenMonHoc, int SoTCLT, int SoTCTH, string MaNganh)
        {
            SqlConnection con = new SqlConnection("Data Source =" + datasource + " ;user id = " + user + "; password = " + password + ";database = " + Namedatabase);
            con.Open();
            string ADD = "insert into MONHOC Values('" + MaMonHoc + "','" + TenMonHoc + "'," + SoTCLT + "," + SoTCTH + ",'" + MaNganh + "')";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = ADD;
            cmd.ExecuteNonQuery();
            con.Close();

        }
        [WebMethod]
        public void SuaMonHoc(string MaMonHoc, string TenMonHoc, int SoTCLT, int SoTCTH, string MaNganh)
        {
            SqlConnection con = new SqlConnection("Data Source =" + datasource + " ;user id = " + user + "; password = " + password + ";database = " + Namedatabase);
            con.Open();
            string ADD = "update MONHOC set TenMonHoc ='" + TenMonHoc + "',SoTCLT =" + SoTCLT + ",SoTCTH =" + SoTCTH + ",MaNganh ='" + MaNganh + "' where MaMon ='" + MaMonHoc + "'";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = ADD;
            cmd.ExecuteNonQuery();
            con.Close();

        }
        [WebMethod]
        public void XoaMonHoc(string MaMon)
        {
            SqlConnection con = new SqlConnection("Data Source =" + datasource + " ;user id = " + user + "; password = " + password + ";database = " + Namedatabase);
            con.Open();
            string ADD = "delete MONHOC where MaMon = '" + MaMon + "' ";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = ADD;
            cmd.ExecuteNonQuery();
            con.Close();

        }
        /**************************************DOI TUONG*******************************************************************/
        [WebMethod]
        public void ThemDoiTuong(string MaDoiTuong, string TenDoiTuong, int TiLegiam)
        {
            SqlConnection con = new SqlConnection("Data Source =" + datasource + " ;user id = " + user + "; password = " + password + ";database = " + Namedatabase);
            con.Open();
            string ADD = "insert into DOITUONG values('" + MaDoiTuong + "','" + TenDoiTuong + "','" + TiLegiam + "') ";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = ADD;
            cmd.ExecuteNonQuery();
            con.Close();

        }
        [WebMethod]
        public void SuaDoiTuong(string MaDoiTuong, string TenDoiTuong, int TiLegiam)
        {
            SqlConnection con = new SqlConnection("Data Source =" + datasource + " ;user id = " + user + "; password = " + password + ";database = " + Namedatabase);
            con.Open();
            string ADD = "update DOITUONG set TenDoiTuong = '" + TenDoiTuong + "',TiLeGiam ='" + TiLegiam + "' where MaDoiTuong = '" + MaDoiTuong + "'";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = ADD;
            cmd.ExecuteNonQuery();
            con.Close();

        }
        [WebMethod]
        public void XoaDoiTuong(string MaDoiTuong)
        {
            SqlConnection con = new SqlConnection("Data Source =" + datasource + " ;user id = " + user + "; password = " + password + ";database = " + Namedatabase);
            con.Open();
            string ADD = "delete DOITUONG where MaDoiTuong ='" + MaDoiTuong + "'";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = ADD;
            cmd.ExecuteNonQuery();
            con.Close();

        }
        /***************************************PHIEU DK MON HOC****************************************************************/
        [WebMethod]
        public void ThemPhieuDKMonHoc(int MaPhieu, int MSSV, int HocKi, string NienKhoa, int SoPhieuThu)
        {
            SqlConnection con = new SqlConnection("Data Source =" + datasource + " ;user id = " + user + "; password = " + password + ";database = " + Namedatabase);
            con.Open();
            string ADD = "insert into PHIEUDKMONHOC Values(" + MaPhieu + "," + MSSV + "," + HocKi + ",'" + NienKhoa + "'," + SoPhieuThu + ")";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = ADD;
            cmd.ExecuteNonQuery();
            con.Close();

        }
        [WebMethod]
        public void SuaPhieuDKMonHoc(int MaPhieu, int MSSV, int HocKi, string NienKhoa, int SoPhieuThu)
        {
            SqlConnection con = new SqlConnection("Data Source =" + datasource + " ;user id = " + user + "; password = " + password + ";database = " + Namedatabase);
            con.Open();
            string ADD = "update PHIEUDKMONHOC set MSSV = " + MSSV + ", HocKi =" + HocKi + ",NienKhoa = '" + NienKhoa + "',SoPhieuThu = " + SoPhieuThu + " where MaPhieuDk = " + MaPhieu;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = ADD;
            cmd.ExecuteNonQuery();
            con.Close();

        }
        [WebMethod]
        public void XoaPhieuDKMonHoc(int MaPhieu)
        {
            SqlConnection con = new SqlConnection("Data Source =" + datasource + " ;user id = " + user + "; password = " + password + ";database = " + Namedatabase);
            con.Open();
            string ADD = "delete PHIEUDKMONHOC where MaPhieuDk = " + MaPhieu;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = ADD;
            cmd.ExecuteNonQuery();
            con.Close();

        }

        /**************************************CTPHEIU DK MON HOC*******************************************************************/
        [WebMethod]
        public void ThemCTPhieuDK(int MaCTPhieu, int MaPhieu, string MaMonHoc)
        {
            SqlConnection con = new SqlConnection("Data Source =" + datasource + " ;user id = " + user + "; password = " + password + ";database = " + Namedatabase);
            con.Open();
            string ADD = "insert into CTPHIEUDKMONHOC values('" + MaCTPhieu + "','" + MaPhieu + "','" + MaMonHoc + "') ";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = ADD;
            cmd.ExecuteNonQuery();
            con.Close();

        }
        [WebMethod]
        public void SuaCTPHIEUDK(int MaCTPhieu, int MaPhieu, string MaMonHoc)
        {
            SqlConnection con = new SqlConnection("Data Source =" + datasource + " ;user id = " + user + "; password = " + password + ";database = " + Namedatabase);
            con.Open();
            string ADD = "update CTPHIEUDKMONHOC set MaPhieuDK = " + MaPhieu + " , MaMonHoc ='" + MaMonHoc + "' where MaCTPHIEUDK = " + MaCTPhieu;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = ADD;
            cmd.ExecuteNonQuery();
            con.Close();

        }
        [WebMethod]
        public void XoaCTPHIEUDK(int MaCT)
        {
            SqlConnection con = new SqlConnection("Data Source =" + datasource + " ;user id = " + user + "; password = " + password + ";database = " + Namedatabase);
            con.Open();
            string ADD = "delete CTPHIEUDKMONHOC where MaCTPHIEUDK = " + MaCT;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = ADD;
            cmd.ExecuteNonQuery();
            con.Close();

        }
        /**************************************PHIEU THU******************************************************************/
        [WebMethod]
        public void ThemPhieuThu(int MaPhieu, int MaPhieuDK, int SoTien, string NgayLap)
        {
            SqlConnection con = new SqlConnection("Data Source =" + datasource + " ;user id = " + user + "; password = " + password + ";database = " + Namedatabase);
            con.Open();
            string ADD = "insert into PHIEUTHU values(" + MaPhieu + "," + MaPhieuDK + "," + SoTien + ",'" + NgayLap + "')";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = ADD;
            cmd.ExecuteNonQuery();
            con.Close();

        }
        [WebMethod]
        public void SuaPhieuThu(int MaPhieu, int MaPhieuDK, int SoTien, string NgayLap)
        {
            SqlConnection con = new SqlConnection("Data Source =" + datasource + " ;user id = " + user + "; password = " + password + ";database = " + Namedatabase);
            con.Open();
            string ADD = "update PHIEUTHU set MaPhieuDK =" + MaPhieuDK + ",SoTienThu = " + SoTien + ",NgayLap='" + NgayLap + "' where MaPhieuThu = " + MaPhieu;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = ADD;
            cmd.ExecuteNonQuery();
            con.Close();

        }
        [WebMethod]
        public void XoaPhieuThu(int MaPhieu)
        {
            SqlConnection con = new SqlConnection("Data Source =" + datasource + " ;user id = " + user + "; password = " + password + ";database = " + Namedatabase);
            con.Open();
            string ADD = "delete PHIEUTHU where MaPhieuThu = " + MaPhieu;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = ADD;
            cmd.ExecuteNonQuery();
            con.Close();

        }
        /**************************************THAM SO******************************************************************/
        [WebMethod]
        public void ThemThamSo(string TenThamSo, int GiaTri)
        {
            SqlConnection con = new SqlConnection("Data Source =" + datasource + " ;user id = " + user + "; password = " + password + ";database = " + Namedatabase);
            con.Open();
            string ADD = "insert into THAMSO values('" + TenThamSo + "'," + GiaTri + ")";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = ADD;
            cmd.ExecuteNonQuery();
            con.Close();

        }
        [WebMethod]
        public void SuaThamSo(string TenThamSo, int GiaTri)
        {
            SqlConnection con = new SqlConnection("Data Source =" + datasource + " ;user id = " + user + "; password = " + password + ";database = " + Namedatabase);
            con.Open();
            string ADD = "update THAMSO set GiaTri = " + GiaTri + " where TenThamSo = '" + TenThamSo + "'";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = ADD;
            cmd.ExecuteNonQuery();
            con.Close();

        }
        [WebMethod]
        public void XoaThamSo(string TenThamSo)
        {
            SqlConnection con = new SqlConnection("Data Source =" + datasource + " ;user id = " + user + "; password = " + password + ";database = " + Namedatabase);
            con.Open();
            string ADD = "delete THAMSO where TenThamSo = '" + TenThamSo + "'";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = ADD;
            cmd.ExecuteNonQuery();
            con.Close();

        }
        /**************************************TAI KHOAN******************************************************************/
        [WebMethod]
        public void ThemTaiKhoan(string Ten, string MatKhau, int key)
        {
            SqlConnection con = new SqlConnection("Data Source =" + datasource + " ;user id = " + user + "; password = " + password + ";database = " + Namedatabase);
            con.Open();
            string ADD = "insert into USERSNAME values('" + Ten + "','" + MatKhau + "'," + key + ")";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = ADD;
            cmd.ExecuteNonQuery();
            con.Close();

        }
        [WebMethod]
        public void SuaTaiKhoan(string Ten, string MatKhau, int key)
        {
            SqlConnection con = new SqlConnection("Data Source =" + datasource + " ;user id = " + user + "; password = " + password + ";database = " + Namedatabase);
            con.Open();
            string ADD = "update USERSNAME set MatKhau='" + MatKhau + "',KeyUser = " + key + " where TenTaiKHoan ='" + Ten + "'";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = ADD;
            cmd.ExecuteNonQuery();
            con.Close();

        }
        [WebMethod]
        public void XoaTaiKhoan(string TenTaiKhoan)
        {
            SqlConnection con = new SqlConnection("Data Source =" + datasource + " ;user id = " + user + "; password = " + password + ";database = " + Namedatabase);
            con.Open();
            string ADD = "delete from USERSNAME where TenTaiKHoan = '" + TenTaiKhoan + "'";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = ADD;
            cmd.ExecuteNonQuery();
            con.Close();

        }
        public void DangNhap(string TenTaiKhoan, string MatKhau)
        {
            SqlConnection con = new SqlConnection("Data Source =" + datasource + " ;user id = " + user + "; password = " + password + ";database = " + Namedatabase);
            con.Open();
            string ADD = "select count(*) from USERSNAME where TenTaiKHoan ='' and MatKhau =''";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = ADD;
            cmd.ExecuteNonQuery();
            con.Close();

        }
    }
}
